package com.android.db;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.android.model.Messageg;

public class DBHelpter extends SQLiteOpenHelper {

	private static String DATABASE_NAME = "sql";
	private static String TABLE_NAME = "data_table";
	private static String TITLE = "title";
	private static String TIME = "time";
	private static String URL = "url";
	private static String FROM = "fromuser";

	public DBHelpter help;

	public DBHelpter(Context context) {
		super(context, DATABASE_NAME, null, 1);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		String sql = "CREATE TABLE if not exists  data_table ( id  INTEGER PRIMARY KEY  AUTOINCREMENT, fromuser TEXT,time  TEXT,  title TEXT, url TEXT);";
		db.execSQL(sql);
		System.out.println("data has been created");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub

	}

	public void Delete() {
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "DELETE FROM data_table";
		db.execSQL(sql);
		System.out.println("data has been delete");
	}

	public void Insert(Messageg messageg) {
		SQLiteDatabase db = this.getWritableDatabase();
		String sql = "select * from data_table where url ='"
				+ messageg.GetUrl() + "'";
		Cursor cursor = db.rawQuery(sql, null);
		System.out.println(cursor.getCount());
		if (cursor.getCount() == 0) {
			ContentValues value = new ContentValues();
			value.put(TITLE, messageg.GetTitle());
			value.put(TIME, messageg.GetTime());
			value.put(URL, messageg.GetUrl());
			value.put(FROM, messageg.GetFrom());

			long id = db.insert(TABLE_NAME, null, value);
			System.out.println("has been insert in db line " + id);
		} else {
			System.out.println("hasn't been insert in db ");
		}
	}

	public List<Messageg> findlist(int flag) {
		List<Messageg> messagegs = new ArrayList<Messageg>();
		SQLiteDatabase db = this.getReadableDatabase();
		String sql = "select * from data_table order by id asc limit '" + flag
				* 12 + "',12";
		Cursor cursor = db.rawQuery(sql, null);

		System.out.println("cursor num is " + cursor.getCount());

		cursor.moveToFirst();

		if (cursor.getCount() > 0) {
			for (int i = 0; i < cursor.getCount(); i++) {
				Messageg messageg = new Messageg();
				messageg.SetTitle(cursor.getString(cursor.getColumnIndex(TITLE)));
				messageg.SetTime(cursor.getString(cursor.getColumnIndex(TIME)));
				messageg.SetFrom(cursor.getString(cursor.getColumnIndex(FROM)));
				messageg.SetUrl(cursor.getString(cursor.getColumnIndex(URL)));
				messagegs.add(messageg);
				cursor.moveToNext();
			}
			return messagegs;
		} else {
			return null;
		}

	}
}
